//
//  YbrLocalCourseDao.m
//  wifibox
//
//  Created by os X 10.9 on 14-12-22.
//  Copyright (c) 2014年 yinbaner. All rights reserved.
//  本地课程的数据

#import "YbrLocalCourseDao.h"
#import "YbrHttpMyBagItem.h"
#import "YbrTextClassInfoItem.h"
#import "YbrTextClassMusicItem.h"
//#import "FilesDownManage.h"

@implementation YbrLocalCourseDao

-(NSArray *) getAll {
    __block NSArray *arr;
    
    [[YbrSQLHelper getDataBase] inDatabase:^(FMDatabase *db) {
        FMResultSet *rs;
        // 1.查询数据
        rs = [db executeQueryWithFormat:@"select * from local_course"];
        
        NSMutableArray *tempArr = [NSMutableArray array];
        while (rs.next) {
            YbrHttpMyBagItem *item = [[YbrHttpMyBagItem alloc] init];
            item.courseName = [rs stringForColumn:@"name"];
            item.courseId = [rs intForColumn:@"courseid"];
            item.img = [rs stringForColumn:@"img"];
            item.channelId = [rs intForColumn:@"channelid"];
            item.channelName = [rs stringForColumn:@"channelname"];
            item.channelType = [rs intForColumn:@"channeltype"];
            [tempArr addObject:item];
        }
        [rs close];
        arr = [tempArr copy];
    }];
    return arr;
}

-(BOOL) setCourseMusic:(YbrMusicItem *) musicItem courseName:(NSString *)courseName courseId:(int) courseId channelId:(int) channelId channelName:(NSString *) channelName channelType:(int) channelType position:(int) position {
    __block BOOL isOk = false;
    
    [[YbrSQLHelper getDataBase] inDatabase:^(FMDatabase *db) {
        
        FMResultSet *rs;
        // 1.查询数据
        rs = [db executeQueryWithFormat:@"select courseid from local_course where courseid=%i" ,courseId];
        if(!rs.next) {
            [db executeUpdate:@"insert into local_course (name ,courseid, img ,channelid ,channelname ,channeltype) VALUES (?,?,?,?,? ,?)",
             courseName ,
             [NSNumber numberWithInt:courseId] ,
             musicItem.img ,
             [NSNumber numberWithInt:channelId] ,
             channelName,
             [NSNumber numberWithInt:channelType]];
        }
        [rs close];
        
        [db executeUpdateWithFormat:@"delete from local_course_Music where musicid=%i" ,musicItem.musicId];
        
        isOk = [db executeUpdate:@"insert into local_course_Music (courseid, name, downloadurl ,file ,duration ,timerstr ,musicid ,sort) VALUES (?,?,?,?,?,?,?,?)",
                [NSNumber numberWithInt:courseId],
                musicItem.musicName ,
                musicItem.downloadurl ,
                musicItem.file,
                [NSNumber numberWithInt:musicItem.duration],
                musicItem.timeStr ,
                [NSNumber numberWithInt:musicItem.musicId],
                [NSNumber numberWithInt:position]];
    }];
    
    return isOk;
}

-(BOOL) setClassCourseMusic:(YbrMusicItem *) musicItem courseName:(NSString *)courseName courseId:(int) courseId channelId:(int) channelId channelName:(NSString *) channelName channelType:(int) channelType position:(int) position textClassInfo:(YbrTextClassInfoItem *) textClassInfo {
    __block BOOL isOk = false;
    
    [[YbrSQLHelper getDataBase] inDatabase:^(FMDatabase *db) {
        
        FMResultSet *rs;
        // 1.查询数据
        rs = [db executeQueryWithFormat:@"select courseid from local_course where courseid=%i" ,courseId];
        if(!rs.next) {
            [db executeUpdate:@"insert into local_course (name ,courseid, img ,channelid ,channelname ,channeltype) VALUES (?,?,?,?,?,?)",
             courseName ,
             [NSNumber numberWithInt:courseId] ,
             musicItem.img ,
             [NSNumber numberWithInt:channelId] ,
             channelName,
             [NSNumber numberWithInt:channelType]];
        }
        [rs close];
        
        [db executeUpdateWithFormat:@"delete from local_second_course_Music where musicid=%i" ,musicItem.musicId];
        
        isOk = [db executeUpdate:@"insert into local_second_course_Music (classid ,classname ,classsort ,courseid ,name , downloadurl ,file ,duration ,timerstr ,musicid ,sort) VALUES (?,?,?,?,?,?,?,?,?,?,?)",
                [NSNumber numberWithInt:textClassInfo._id] ,
                textClassInfo.name ,
                [NSNumber numberWithInt:textClassInfo.sort],
                [NSNumber numberWithInt:courseId],
                musicItem.musicName ,
                musicItem.downloadurl ,
                musicItem.file,
                [NSNumber numberWithInt:musicItem.duration],
                musicItem.timeStr ,
                [NSNumber numberWithInt:musicItem.musicId],
                [NSNumber numberWithInt:position]];
    }];
    
    return isOk;
}


-(NSString *) getCourseNameById:(int) courseId {
    __block NSString *courseName;
    [[YbrSQLHelper getDataBase] inDatabase:^(FMDatabase *db) {
        FMResultSet *rs;
        rs = [db executeQueryWithFormat:@"select name from local_course where courseid=%i" ,courseId];
        
        while (rs.next) {
            courseName = [rs stringForColumn:@"name"];
        }
        
        [rs close];
    }];
    return courseName;

}

// YbrMusicItem
-(NSArray *) getCourseMusicById:(YbrHttpMyBagItem *) myBagItem {
    __block NSArray *arr;
    
    [[YbrSQLHelper getDataBase] inDatabase:^(FMDatabase *db) {
        FMResultSet *rs;
        // 1.查询数据
        rs = [db executeQueryWithFormat:@"select * from local_course_Music where courseid=%i order by sort" ,myBagItem.courseId];
        
        NSMutableArray *tempArr = [NSMutableArray array];
        while (rs.next) {
            YbrMusicItem *item = [[YbrMusicItem alloc] init];
            
            item.musicId = [rs intForColumn:@"musicid"];
            item.musicName = [rs stringForColumn:@"name"];
            item.downloadurl = [rs stringForColumn:@"downloadurl"];
            item.file = [rs stringForColumn:@"file"];
            item.timeStr = [rs stringForColumn:@"timerstr"];
            item.duration = [rs intForColumn:@"duration"];
            item.singername = myBagItem.courseName;
            item.albumname = myBagItem.channelName;
            
            [tempArr addObject:item];
        }
        [rs close];
        arr = [tempArr copy];
    }];
    return arr;
}

-(NSArray *) getClassCourseMusicById:(YbrHttpMyBagItem *) myBagItem {
    __block NSArray *arr;
    
    [[YbrSQLHelper getDataBase] inDatabase:^(FMDatabase *db) {
        FMResultSet *rs;
        // 1.查询数据
        rs = [db executeQueryWithFormat:@"select * from local_second_course_Music where courseid=%i order by classsort, sort" ,myBagItem.courseId];
        
        NSMutableArray *tempArr = [NSMutableArray array];
        YbrTextClassInfoItem *textClassInfo;
        NSMutableArray *audioTempArr;
        int classid = -1;
        while (rs.next) {
            int cursorClassId = [rs intForColumn:@"classid"];
            
            if(cursorClassId != classid) {
                textClassInfo = [[YbrTextClassInfoItem alloc] init];
                textClassInfo.courseId = [rs intForColumn:@"courseid"];
                textClassInfo._id = cursorClassId;
                textClassInfo.sort = [rs intForColumn:@"classsort"];
                textClassInfo.name = [rs stringForColumn:@"classname"];
                textClassInfo.isOpen = YES;
                audioTempArr = [NSMutableArray array];
                textClassInfo.audiolist = audioTempArr;
                [tempArr addObject:textClassInfo];
                classid = cursorClassId;
            }
            
            YbrTextClassMusicItem *textClassMusicItem = [[YbrTextClassMusicItem alloc] init];
            textClassMusicItem._id = [rs intForColumn:@"musicid"];
            textClassMusicItem.audio_url = [rs stringForColumn:@"downloadurl"];
            textClassMusicItem.name = [rs stringForColumn:@"name"];
            textClassMusicItem.timelength = [rs stringForColumn:@"timerstr"];
            textClassMusicItem.file = [rs stringForColumn:@"file"];
            textClassMusicItem.sort = [rs intForColumn:@"sort"];
            [audioTempArr addObject:textClassMusicItem];
        }
        [rs close];
        arr = [tempArr copy];
    }];
    
    return arr;
}


-(NSString *) getCourseImgById:(int) courseId {
    __block NSString *courseImg;
    [[YbrSQLHelper getDataBase] inDatabase:^(FMDatabase *db) {
        FMResultSet *rs;
        rs = [db executeQueryWithFormat:@"select img from local_course where courseid=%i" ,courseId];
        
        while (rs.next) {
            courseImg = [rs stringForColumn:@"musicstr"];
        }
        
        [rs close];
    }];
    return courseImg;
}

-(BOOL) deleCourseById:(int) courseId {
    __block BOOL isOk = false;
    
    [[YbrSQLHelper getDataBase] inTransaction:^(FMDatabase *db, BOOL *rollback) {
        FMResultSet *courseRs = [db executeQueryWithFormat:@"select channeltype from local_course where courseid=%i" ,courseId];
        
        BOOL isSecondType = NO;
        if(courseRs.next) {
            if([courseRs intForColumn:@"channeltype"] == SecondType) {
                isSecondType = YES;
            }
        }
        [courseRs close];
        
        FMResultSet *rs;
        if(isSecondType) {
            rs = [db executeQueryWithFormat:@"select file ,musicid from local_second_course_Music where courseid=%i" ,courseId];
        } else {
            rs = [db executeQueryWithFormat:@"select file ,musicid from local_course_Music where courseid=%i" ,courseId];
        }
        
        while (rs.next) {
            NSString *file = [rs stringForColumn:@"file"];
            int musicid = [rs intForColumn:@"musicid"];
            
//            [[FilesDownManage sharedFilesDownManage] deleteFinishsFileByMusicId:[NSString stringWithFormat:@"%i" ,musicid]];
            
            NSFileManager *fileMgr = [NSFileManager defaultManager];
            BOOL bRet = [fileMgr fileExistsAtPath:file];
            if(bRet) {
                [fileMgr removeItemAtPath:file error:nil];
            }
            [db executeUpdateWithFormat:@"delete from down_over where music_id=%i" ,musicid];
        }
        [rs close];
        
        [db executeUpdateWithFormat:@"delete from local_course where courseid=%i",courseId];
        if(isSecondType) {
            isOk = [db executeUpdateWithFormat:@"delete from local_second_course_Music where courseid=%i" ,courseId];
        } else {
            isOk = [db executeUpdateWithFormat:@"delete from local_course_Music where courseid=%i" ,courseId];
        }
    }];
    return isOk;
}

-(BOOL) deleCourseMusicById:(int) musicid courseId:(int) courseid {
    __block BOOL isOk = false;
    
    [[YbrSQLHelper getDataBase] inDatabase:^(FMDatabase *db) {
        
        FMResultSet *rs;
        rs = [db executeQueryWithFormat:@"select file from local_course_Music where courseid=%i and musicid=%i" ,courseid ,musicid];
        
        while (rs.next) {
            NSString *file = [rs stringForColumn:@"file"];
        
//            [[FilesDownManage sharedFilesDownManage] deleteFinishsFileByMusicId:[NSString stringWithFormat:@"%i" ,musicid]];
            
            NSFileManager *fileMgr = [NSFileManager defaultManager];
            
            BOOL bRet = [fileMgr fileExistsAtPath:file];
            if(bRet) {
                [fileMgr removeItemAtPath:file error:nil];
            }
            [db executeUpdateWithFormat:@"delete from down_over where music_id=%i" ,musicid];
            
            isOk = [db executeUpdateWithFormat:@"delete from local_course_Music where courseid=%i and musicid=%i", courseid ,musicid];
        }
        
        [rs close];
        
    }];
    return isOk;
}

-(BOOL) deleClassCourseMusicById:(int) musicid courseId:(int) courseid {
    __block BOOL isOk = false;
    
    [[YbrSQLHelper getDataBase] inDatabase:^(FMDatabase *db) {
        
        FMResultSet *rs;
        rs = [db executeQueryWithFormat:@"select file from local_second_course_Music where courseid=%i and musicid=%i" ,courseid ,musicid];
        
        while (rs.next) {
            NSString *file = [rs stringForColumn:@"file"];
            
//            [[FilesDownManage sharedFilesDownManage] deleteFinishsFileByMusicId:[NSString stringWithFormat:@"%i" ,musicid]];
            
            NSFileManager *fileMgr = [NSFileManager defaultManager];
            BOOL bRet = [fileMgr fileExistsAtPath:file];
            if(bRet) {
                [fileMgr removeItemAtPath:file error:nil];
            }
            [db executeUpdateWithFormat:@"delete from down_over where music_id=%i" ,musicid];
            
            isOk = [db executeUpdateWithFormat:@"delete from local_second_course_Music where courseid=%i and musicid=%i", courseid ,musicid];
        }
        
        [rs close];
        
    }];
    return isOk;
}


-(BOOL) deleCourseMusicOnlyDBById:(int) musicid {
    __block BOOL isOk = false;
    
    [[YbrSQLHelper getDataBase] inDatabase:^(FMDatabase *db) {
         isOk = [db executeUpdateWithFormat:@"delete from local_course_Music where musicid=%i" ,musicid];
    }];
    return isOk;

}

@end
